package com.housekeeping.dao.impl;

import com.housekeeping.dao.HousekeepingServiceDAO;
import com.housekeeping.model.HousekeepingService;
import com.housekeeping.utils.DBUtils;
import java.util.List;

public class HousekeepingServiceDAOImpl implements HousekeepingServiceDAO {

    @Override
    public HousekeepingService findById(Integer serviceId) {
        String sql = "SELECT s.service_id as serviceId, s.service_name as serviceName, s.description, s.category_id as categoryId, " +
                    "s.price, s.unit, s.service_time as serviceTime, s.status, s.create_time as createTime, s.update_time as updateTime, " +
                    "c.category_name as categoryName FROM housekeeping_services s " +
                    "LEFT JOIN categories c ON s.category_id = c.category_id " +
                    "WHERE s.service_id = ?";
        return DBUtils.queryForObject(sql, HousekeepingService.class, serviceId);
    }

    @Override
    public List<HousekeepingService> findAll() {
        String sql = "SELECT s.service_id as serviceId, s.service_name as serviceName, s.description, s.category_id as categoryId, " +
                    "s.price, s.unit, s.service_time as serviceTime, s.status, s.create_time as createTime, s.update_time as updateTime, " +
                    "c.category_name as categoryName FROM housekeeping_services s " +
                    "LEFT JOIN categories c ON s.category_id = c.category_id " +
                    "ORDER BY s.create_time DESC";
        return DBUtils.queryForList(sql, HousekeepingService.class);
    }

    @Override
    public List<HousekeepingService> findByCategoryId(Integer categoryId) {
        String sql = "SELECT s.service_id as serviceId, s.service_name as serviceName, s.description, s.category_id as categoryId, " +
                    "s.price, s.unit, s.service_time as serviceTime, s.status, s.create_time as createTime, s.update_time as updateTime, " +
                    "c.category_name as categoryName FROM housekeeping_services s " +
                    "LEFT JOIN categories c ON s.category_id = c.category_id " +
                    "WHERE s.category_id = ? ORDER BY s.create_time DESC";
        return DBUtils.queryForList(sql, HousekeepingService.class, categoryId);
    }

    @Override
    public List<HousekeepingService> findByStatus(Integer status) {
        String sql = "SELECT s.service_id as serviceId, s.service_name as serviceName, s.description, s.category_id as categoryId, " +
                    "s.price, s.unit, s.service_time as serviceTime, s.status, s.create_time as createTime, s.update_time as updateTime, " +
                    "c.category_name as categoryName FROM housekeeping_services s " +
                    "LEFT JOIN categories c ON s.category_id = c.category_id " +
                    "WHERE s.status = ? ORDER BY s.create_time DESC";
        return DBUtils.queryForList(sql, HousekeepingService.class, status);
    }

    @Override
    public List<HousekeepingService> findAvailableServices() {
        return findByStatus(1);
    }

    @Override
    public List<HousekeepingService> searchByKeyword(String keyword) {
        String sql = "SELECT s.service_id as serviceId, s.service_name as serviceName, s.description, s.category_id as categoryId, " +
                    "s.price, s.unit, s.service_time as serviceTime, s.status, s.create_time as createTime, s.update_time as updateTime, " +
                    "c.category_name as categoryName FROM housekeeping_services s " +
                    "LEFT JOIN categories c ON s.category_id = c.category_id " +
                    "WHERE s.status = 1 AND (s.service_name LIKE ? OR s.description LIKE ?) " +
                    "ORDER BY s.create_time DESC";
        String searchKeyword = "%" + keyword + "%";
        return DBUtils.queryForList(sql, HousekeepingService.class, searchKeyword, searchKeyword);
    }

    @Override
    public int insert(HousekeepingService service) {
        String sql = "INSERT INTO housekeeping_services (service_name, description, category_id, " +
                    "price, unit, service_time, status) VALUES (?, ?, ?, ?, ?, ?, ?)";
        return DBUtils.update(sql, service.getServiceName(), service.getDescription(),
                service.getCategoryId(), service.getPrice(), service.getUnit(),
                service.getServiceTime(), service.getStatus());
    }

    @Override
    public int update(HousekeepingService service) {
        String sql = "UPDATE housekeeping_services SET service_name = ?, description = ?, " +
                    "category_id = ?, price = ?, unit = ?, service_time = ?, status = ?, " +
                    "update_time = NOW() WHERE service_id = ?";
        return DBUtils.update(sql, service.getServiceName(), service.getDescription(),
                service.getCategoryId(), service.getPrice(), service.getUnit(),
                service.getServiceTime(), service.getStatus(), service.getServiceId());
    }

    @Override
    public int delete(Integer serviceId) {
        String sql = "DELETE FROM housekeeping_services WHERE service_id = ?";
        return DBUtils.update(sql, serviceId);
    }

    @Override
    public int updateStatus(Integer serviceId, Integer status) {
        String sql = "UPDATE housekeeping_services SET status = ?, update_time = NOW() WHERE service_id = ?";
        return DBUtils.update(sql, status, serviceId);
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM housekeeping_services";
        Object result = DBUtils.queryForScalar(sql);
        return result != null ? ((Number) result).longValue() : 0;
    }
} 